package com.zzlzd.drp.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author ceshi
 * @Title:
 * @Package
 * @Description:
 * @date 2021-3-1919:39
 */
public interface OrdersRep extends JpaRepository<OrdersDto,Integer> {


    @Query(nativeQuery=true, value=" SELECT * FROM orders WHERE user_id = ?1 AND id = ?2")
    public Map<String, Object> findOrdersMap(Integer userId, String id);


    @Query(nativeQuery=true, value=" SELECT * FROM orders WHERE user_id = ?1 AND id = ?2")
    public OrdersDto findOrders(Integer userId, String id);

    @Query(nativeQuery=true, value=" SELECT u.head,u.phone,u.`name`,o.* FROM orders o LEFT JOIN user u on o.user_id = u.id order by state asc, id desc")
    public List<Map<String,Object>> findOrder();

    @Query(nativeQuery=true, value=" SELECT * FROM orders WHERE id = ?1 AND state = 0")
    public OrdersDto findOrderById(String id);

    @Query(nativeQuery=true, value=" SELECT * FROM (SELECT COUNT(id) daifahuo FROM orders WHERE state = 0 ) a,(SELECT COUNT(id) daishouhuo FROM orders WHERE state = 1 ) b, (SELECT COUNT(id) yiwancheng FROM orders WHERE state = 2 )c")
    Map<String, Integer> orderChars();
}
